package com.base.controller;

import java.io.PrintWriter;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.base.comm.CommUrls;
import com.base.po.voip.*;
import com.base.service.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;
import com.base.comm.CommContent;
import com.base.po.voip.UsersExample.Criteria;
import com.base.utils.IP;
import com.base.utils.PasswordUtils;
import com.base.utils.Produce;

@Controller
@RequestMapping("/admin")
public class content_xiugaishangjia {
    @Autowired
    private LogsService logsService;// log日志
    @Autowired
    private UsersService usersService;
    @Autowired
    private FreeroutesService freeroutesService;
    @Autowired
    private SmsconfigsService smsconfigsService;
    @Autowired
    private AppAuthService appAuthService;

    // 账户信息
    @RequestMapping("/content_xiugaishangjia")
    public ModelAndView index(HttpServletRequest request, @RequestParam(value = "id", required = true) String id,
                              Integer choose) throws Exception {
        // 返回ModelAndView
        ModelAndView modelAndView = new ModelAndView();
        if (id == null) {
            modelAndView.setViewName("jsp/datanotfound");
            return modelAndView;
        }
        // 获取登陆ID
        String adminUserId = (String) request.getSession().getAttribute("adminUserId");
        Integer adminUsergrade = (Integer) request.getSession().getAttribute("adminUsergrade");
        Users users = usersService.selectByPrimaryKey(id);
        if (users == null || users.getId() == null) {
            modelAndView.setViewName("jsp/datanotfound");
            return modelAndView;
        }
        // 获取所有费率
        FreeroutesExample freeroutesExample = new FreeroutesExample();
        freeroutesExample.setOrderByClause("CreateTime");
        freeroutesExample.getOredCriteria().add(freeroutesExample.createCriteria().andDatastatusEqualTo(true));
        List<Freeroutes> freeroutes = freeroutesService.selectByExample(freeroutesExample);
        // 获取短信的配置
        Smsconfigs smsconfigs = smsconfigsService.selectByAgentId(id);
        if (smsconfigs == null) {
            Smsconfigs smsRecord = new Smsconfigs(Produce.getUUID(), id,
                    "http://www.zypaas.com:9988/V1/Account/ACCf472db2f5af843538184f7286e261a57/sms/sureTempalteSend", 0,
                    "mtl4r3025VTI852j", null, "mtlg94eI81V6Vs3r", "mtl4bW0612032418", true, 2, "application/json");
            smsconfigsService.insert(smsRecord);
            smsconfigs = smsRecord;
        }
        // TODO 代理商信息
        Users pUsers = usersService.selectByPrimaryKey(users.getParentid());
        // TODO 上级商家信息
        Users sUsers = usersService.selectByPrimaryKey(users.getShopid());
        // 设置页面进行跳转
        request.setAttribute("freeroutes", freeroutes);
        request.setAttribute("id", id);
        if (pUsers != null) {
            request.setAttribute("parentid", pUsers.getUsername());
        } else {
            request.setAttribute("parentid", "");
        }
        if (choose == 2) {
            if (sUsers != null) {
                request.setAttribute("shopid", sUsers.getUsername());
            } else {
                request.setAttribute("shopid", "");
            }
        }
        //如果用户的等级是admin则可以执行修改操作
        if (adminUsergrade == 0) {
            AppAuth appAuth = appAuthService.selectByUserId(users.getId());
            if (appAuth == null) {
                appAuth = new AppAuth(users.getId(), false);
                appAuthService.insertSelective(appAuth);
            }
            request.setAttribute("appAuth", appAuth.getIsauth());
        }
        request.setAttribute("username", users.getUsername());
        request.setAttribute("password", PasswordUtils.decodePWD(users.getPassword()));
        request.setAttribute("freeroute", users.getFreeroute());
        request.setAttribute("nextagent", users.getNextagent());
        request.setAttribute("pic", users.getPic());
        request.setAttribute("calltype", users.getCalltype());
        request.setAttribute("smstype", users.getSmstype());
        request.setAttribute("addcard", users.getAddcard());
        request.setAttribute("adid", users.getAdid());
        request.setAttribute("pid", users.getPid());
        request.setAttribute("isprofit", users.getIsprofit());
        request.setAttribute("isalipay", users.getIsalipay());
        request.setAttribute("isaliprofit", users.getIsaliprofit());
        request.setAttribute("email", users.getEmail());
        request.setAttribute("aliprofitratio", users.getAliprofitratio());
        request.setAttribute("dkbl", users.getDkbl());
        request.setAttribute("regimoney", users.getRegimoney());
        request.setAttribute("blance", users.getBlance());
        request.setAttribute("sqappkey", users.getSqappkey());
        request.setAttribute("overdraft", users.getOverdraft());
        request.setAttribute("shopblance", users.getShopblance());
        request.setAttribute("agentlogo", users.getAgentlogo());
        request.setAttribute("linebalance", users.getLinebalance());
        request.setAttribute("airrecharge", users.getAirrecharge());
        request.setAttribute("adminUsergrade", adminUsergrade);
        request.setAttribute("igtype", users.getIgtype());
        request.setAttribute("isgrounding", users.getIsgrounding());
        request.setAttribute("isupdateshop", users.getIsupdateshop());
        request.setAttribute("ismember", users.getIsmember());
        request.setAttribute("issuper", users.getIssuper());
        request.setAttribute("isdialogbox", users.getIsdialogbox());
        request.setAttribute("theme", users.getTheme() == null ? 0 : users.getTheme());
        request.setAttribute("balance", users.getBalance());
        request.setAttribute("browse", users.getBrowse());
        request.setAttribute("jhappid", users.getJhappid());
        request.setAttribute("jhappkey", users.getJhappkey());
        if (request.getSession().getAttribute("adminUserId") == null) {
            modelAndView.setViewName("jsp/login");
        } else {
            // 前端传choose 1流量管理 2商家管理
            if (choose == 1) {
                request.setAttribute("signid", smsconfigs.getSignid());
                request.setAttribute("smsnum", smsconfigs.getCount());
                modelAndView.setViewName("jsp/content_xiugaioem");
            } else if (choose == 2) {
                modelAndView.setViewName("jsp/content_xiugaishangjia");
            }
        }
        return modelAndView;
    }

    String SUCCESS = "success";

    // 更改商户信息
    @RequestMapping(value = "/updateUsers", method = RequestMethod.POST)
    public void updateUsers(HttpServletRequest request, HttpServletResponse response,
                            @RequestParam(value = "id", required = true) String id, String username, String password, String email,
                            Date createtime, Date logintime, String loginip, Integer role, Boolean datastatus, String mobile,
                            BigDecimal blance, String freeroute, String parentid, String agentlogo, String prefix, String theme,
                            Boolean addcard, Boolean nextagent, String androidversion, String androidpath, String iosversion,
                            String iospath, Integer regimoney, Integer regishopmoney, Date endtime, String pic, BigDecimal dkbl,
                            Boolean isshop, Integer type, String shopid, String adid, BigDecimal profitratio, String appkey,
                            String appsecret, BigDecimal linebalance, String pid, String dappkey, String sqappkey, Integer overdraft,
                            String appstore, String appid, String keypath, String agentprefix, String oneagent, Boolean sxxh,
                            Boolean isauth, Boolean isalipay, Boolean isprofit, Boolean isaliprofit, BigDecimal aliprofitratio,
                            BigDecimal aliprofitmoney, BigDecimal profitmoney, Integer shopblance, Integer calltype, Integer smstype,
                            String pass, String signid, Integer count, Integer igtype, Integer regitime, Byte airrecharge, Byte isgrounding,
                            Byte isupdateshop, Byte ismember, Byte isdialogbox, Integer browse, BigDecimal balance,
                            String jhappid, String jhappkey, Integer appAuth,Integer choose // choose 1 oem修改 2商家修改 3修改密码
    ) throws Exception {
        // 返回参数
        response.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=UTF-8");
        String adminUserId = (String) request.getSession().getAttribute("adminUserId");
        Integer adminUsergrade = (Integer) request.getSession().getAttribute("adminUsergrade");
        List<Users> user = null;
        // TODO 查询所有用户 老数据里没被冻结的用户
        if (choose == 1 || choose == 2) {
            UsersExample usersExample = new UsersExample();
//			Users user = usersService.selectByUsername(username);
            Criteria criteria = usersExample.createCriteria();
            criteria.andUsernameEqualTo(username).andDatastatusEqualTo(true);
            user = usersService.selectByExample(usersExample);
        }

        PrintWriter out = response.getWriter();
        String usernamesys = (String) request.getSession().getAttribute("useracc");
        if (user != null && user.size() > 0) {
            if (user != null && !id.equals(user.get(0).getId())) {
                logsService.insert(CommContent.TYPE_CREATE, "商户名：" + username, usernamesys + "已存在", true,
                        IP.getIpAddr(request));
                out.print("账号名已存在，请重新填写");
                return;
            }
        }
        if (password == null || password.equals("")) {
            out.print("密码不能为空");
            return;
        }

        if (adminUsergrade == 0 && choose != 3) {
            if (!parentid.trim().equals("admin") && parentid != null && !parentid.equals("")) {
                if (choose == 1 || choose == 2) {
                    List<Users> pUsers = null;
                    // TODO 需要修改的代理商信息
                    pUsers = usersService.selectByUserName(parentid);
                    if (pUsers == null) {
                        out.print("代理商不存在");
                        return;
                    } else if (pUsers.get(0).getRole() != 1) {
                        out.print("请输入正确的代理商名");
                        return;
                    }
                    parentid = pUsers.get(0).getId();
                    System.out.println(parentid);
                } else {
                    out.print("代理商名不为空");
                    return;
                }
            }
        } else {
            parentid = null;
        }

        if (adminUsergrade == 0 || adminUsergrade == 1) {
            // TODO 需要修改的商家信息
            List<Users> sUsers = null;
            if (shopid != null && !shopid.equals("")) {
                sUsers = usersService.selectByUserName(shopid);
                if (sUsers == null || sUsers.size() == 0) {
                    out.print("商家不存在");
                    return;
                }
                List<String> shopId = usersService.selectParentIdByShopiId(sUsers.get(0).getId());
                if (adminUsergrade == 1) {
                    if (!sUsers.get(0).getId().equals(adminUserId)) {
                        System.out.println(shopId.get(0));
                        if (!shopId.get(0).equals(adminUserId)) {
                            out.print("该商家不是该代理商下商家");
                            return;
                        }
                    }
                }
                shopid = sUsers.get(0).getId();
            }
        } else {
            shopid = null;
        }

        if (theme != null && !theme.equals("") && theme.equals("undefined")) {
            theme = null;
        }

        if (id == null) {
            out.print("浏览器在点击的过程中发现丢失了用户信息，请您刷新重试！");
            return;
        } else {
            Users record = new Users(id.trim(), username, PasswordUtils.toPWD(password), email, createtime, logintime,
                    loginip, role, datastatus, mobile, blance, freeroute, parentid, agentlogo, prefix, theme, addcard,
                    nextagent, airrecharge, androidversion, androidpath, iosversion, iospath, regimoney, regishopmoney, regitime,
                    endtime, pic, dkbl, isshop, type, shopid, adid, profitratio, appkey, appsecret, linebalance, pid, dappkey,
                    sqappkey, overdraft, appstore, appid, keypath, agentprefix, oneagent, sxxh, isauth, isalipay,
                    isprofit, isaliprofit, aliprofitratio, aliprofitmoney, profitmoney, shopblance, igtype, calltype,
                    smstype, isgrounding, isupdateshop, ismember, isdialogbox, browse, balance);
            record.setJhappid(jhappid);
            record.setJhappkey(jhappkey);
            int code = usersService.updateByPrimaryKeySelective(record);

            if (adminUsergrade == 0 && user.get(0).getRole() == 1 && !user.get(0).getCalltype().equals(calltype)) {
                updateAllShop(user.get(0).getId(), calltype);
            }
            if(appAuth!=null){
                AppAuth appAuth1=appAuthService.selectByUserId(id.trim());
                if (appAuth == 1){
                    appAuth1.setIsauth(true);
                }else if (appAuth == 0){
                    appAuth1.setIsauth(false);
                }
               appAuthService.updateByPrimaryKeySelective(appAuth1);
            }
            // TODO 修改短信的条数
            // 获取短信的配置
            if (choose == 1) {
                Smsconfigs smsconfigs = smsconfigsService.selectByAgentId(id);
                smsconfigs.setSignid(signid);
                if (smsconfigs.getCount() == null) {
                    smsconfigs.setCount(0);
                }
                if (count == null) {
                    count = 0;
                }
                smsconfigs.setCount(smsconfigs.getCount() + count);
                int codes = smsconfigsService.updateByPrimaryKeySelective(smsconfigs);
                if (code == 1 && codes == 1) {
                    logsService.insert(CommContent.TYPE_UPDATE, "成功更新了" + record.getUsername() + "的账号信息", usernamesys,
                            true, IP.getIpAddr(request));
                    out.print(SUCCESS);
                } else {
                    logsService.insert(CommContent.TYPE_UPDATE, "更新" + record.getUsername() + "的账号信息时失败了", usernamesys,
                            true, IP.getIpAddr(request));
                    out.print("更改失败，请您重试");
                }
            }

            if (choose == 2) {
                if (code == 1) {
                    logsService.insert(CommContent.TYPE_UPDATE, "成功更新了" + record.getUsername() + "的账号信息", usernamesys,
                            true, IP.getIpAddr(request));
                    out.print(SUCCESS);
                } else {
                    logsService.insert(CommContent.TYPE_UPDATE, "更新" + record.getUsername() + "的账号信息时失败了", usernamesys,
                            true, IP.getIpAddr(request));
                    out.print("更改失败，请您重试");
                }
            }

            if (choose == 3) {
                if (code == 1) {
                    logsService.insert(CommContent.TYPE_UPDATE, "成功修改了" + record.getUsername() + "的账号密码", usernamesys,
                            true, IP.getIpAddr(request));
                    out.print(SUCCESS);
                } else {
                    logsService.insert(CommContent.TYPE_UPDATE, "修改" + record.getUsername() + "的账号密码时失败了", usernamesys,
                            true, IP.getIpAddr(request));
                    out.print("更改失败，请您重试");
                }
            }

        }
    }

    // 更改商户信息
    @RequestMapping(value = "/updateDongjieAndJiedongUsers", method = RequestMethod.POST)
    public void updateDongjieAndJiedongUsers(HttpServletRequest request, HttpServletResponse response,
                                             @RequestParam(value = "id", required = true) String id, String username, String password, String email,
                                             Date createtime, Date logintime, String loginip, Integer role, Boolean datastatus, String mobile,
                                             BigDecimal blance, String freeroute, String parentid, String agentlogo, String prefix, String theme,
                                             Boolean addcard, Boolean nextagent, String androidversion, String androidpath, String iosversion,
                                             String iospath, Integer regimoney, Date endtime, String pic, BigDecimal dkbl, Boolean isshop, Integer type,
                                             String shopid, String adid, BigDecimal profitratio, String appkey, String appsecret, BigDecimal linebalance,
                                             String pid, String dappkey, String sqappkey, Integer overdraft, String appstore, String appid,
                                             String keypath, String agentprefix, String oneagent, Boolean sxxh, Boolean isauth, Boolean isalipay,
                                             Boolean isprofit, Boolean isaliprofit, BigDecimal aliprofitratio, BigDecimal aliprofitmoney,
                                             BigDecimal profitmoney, Integer shopblance, Integer igtype, Integer calltype, String pass,
                                             Integer regishopmoney, Integer regitime, Byte airrecharge, Byte isgrounding, Byte isupdateshop,
                                             Byte isdialogbox, Integer smstype, Byte ismember, Integer browse) throws Exception {
        // 返回参数
        response.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=UTF-8");
        String parent = (String) request.getSession().getAttribute("adminUserId");

        Users user = usersService.selectByPrimaryKey(id);

        PrintWriter out = response.getWriter();
        String usernamesys = (String) request.getSession().getAttribute("useracc");

        if (id == null) {
            out.print("浏览器在点击的过程中发现丢失了用户信息，请您刷新重试！");
            return;
        } else {
            Users record = new Users(id.trim(), username, PasswordUtils.toPWD(password), email, createtime, logintime,
                    loginip, role, datastatus, mobile, blance, freeroute, parentid, agentlogo, prefix, theme, addcard,
                    nextagent, airrecharge, androidversion, androidpath, iosversion, iospath, regimoney, regishopmoney, regitime,
                    endtime, pic, dkbl, isshop, type, shopid, adid, profitratio, appkey, appsecret, linebalance, pid, dappkey,
                    sqappkey, overdraft, appstore, appid, keypath, agentprefix, oneagent, sxxh, isauth, isalipay,
                    isprofit, isaliprofit, aliprofitratio, aliprofitmoney, profitmoney, shopblance, igtype, calltype,
                    smstype, isgrounding, isupdateshop, ismember, isdialogbox, browse);
            int code = usersService.updateByPrimaryKeySelective(record);
            if (datastatus == false) {
                if (code == 1) {
                    logsService.insert(CommContent.TYPE_UPDATE, "成功冻结了" + user.getUsername() + "的账号", usernamesys, true,
                            IP.getIpAddr(request));
                    out.print(SUCCESS);
                } else {
                    logsService.insert(CommContent.TYPE_UPDATE, "冻结" + user.getUsername() + "的账号时失败了", usernamesys,
                            true, IP.getIpAddr(request));
                    out.print("更改失败，请您重试");
                }
            }
            if (datastatus == true) {
                if (code == 1) {
                    logsService.insert(CommContent.TYPE_UPDATE, "成功解冻了" + user.getUsername() + "的账号", usernamesys, true,
                            IP.getIpAddr(request));
                    out.print(SUCCESS);
                } else {
                    logsService.insert(CommContent.TYPE_UPDATE, "解冻" + user.getUsername() + "的账号时失败了", usernamesys,
                            true, IP.getIpAddr(request));
                    out.print("更改失败，请您重试");
                }
            }
        }
    }

    // 更改商户信息
    @RequestMapping(value = "/updateHuaZhangUsers", method = RequestMethod.POST)
    public void updateHuaZhangUsers(HttpServletRequest request, HttpServletResponse response,
                                    @RequestParam(value = "id", required = true) String id, String username, String password, String email,
                                    Date createtime, Date logintime, String loginip, Integer role, Boolean datastatus, String mobile,
                                    BigDecimal blance, String freeroute, String parentid, String agentlogo, String prefix, String theme,
                                    Boolean addcard, Boolean nextagent, String androidversion, String androidpath, String iosversion,
                                    String iospath, Integer regimoney, Date endtime, String pic, BigDecimal dkbl, Boolean isshop, Integer type,
                                    String shopid, String adid, BigDecimal profitratio, String appkey, String appsecret, BigDecimal linebalance,
                                    String pid, String dappkey, String sqappkey, Integer overdraft, String appstore, String appid,
                                    String keypath, String agentprefix, String oneagent, Boolean sxxh, Boolean isauth, Boolean isalipay,
                                    Boolean isprofit, Boolean isaliprofit, BigDecimal aliprofitratio, BigDecimal aliprofitmoney,
                                    BigDecimal profitmoney, Integer shopblance, Integer igtype, Integer calltype, String pass,
                                    Integer regishopmoney, Integer regitime, Byte airrecharge, Byte isgrounding, Byte isupdateshop,
                                    Byte isdialogbox, Integer smstype, Byte ismember, Integer browse, BigDecimal balance) throws Exception {
        // 返回参数
        response.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=UTF-8");
        String parent = (String) request.getSession().getAttribute("adminUserId");
        Users userOem = usersService.selectByPrimaryKey(parent);
        Users user = usersService.selectByPrimaryKey(id);

        PrintWriter out = response.getWriter();

        BigDecimal blanceAddPass = null;
        Integer shopblanceAddPass = null;
        int choose = 0;
        int blanceChoose = 0;
        if (pass != null) {
            if (blance != null) {
                blanceAddPass = blance.add(new BigDecimal(pass));
                choose = 0;
                if (userOem.getRole() == 1) {
                    if (userOem.getBalance().compareTo(new BigDecimal(pass)) == 1
                            || userOem.getBalance().compareTo(new BigDecimal(pass)) == 0) {
                        userOem.setBalance(userOem.getBalance().subtract(new BigDecimal(pass)));
                        choose = 0;
                        blanceChoose = 1;
                    } else {
                        out.print("可用余额不足");
                        choose = 1;
                    }
                }
            }
            if (shopblance != null) {
                shopblanceAddPass = shopblance + Integer.valueOf(pass);
                choose = 0;
                if (userOem.getRole() == 1) {
                    if (userOem.getShopblance() >= Integer.valueOf(pass)) {
                        userOem.setShopblance(userOem.getShopblance() - Integer.valueOf(pass));
                        choose = 0;
                        blanceChoose = 2;
                    } else {
                        out.print("账户购物余额不足");
                        choose = 1;
                    }
                }
            }
        }
        if (choose == 0) {
            if (id == null) {
                out.print("浏览器在点击的过程中发现丢失了用户信息，请您刷新重试！");
                return;
            } else {
                Users record = new Users(id.trim(), username, PasswordUtils.toPWD(password), email, createtime,
                        logintime, loginip, role, datastatus, mobile, null, freeroute, parentid, agentlogo,
                        prefix, theme, addcard, nextagent, airrecharge, androidversion, androidpath, iosversion, iospath, regimoney,
                        regishopmoney, regitime, endtime, pic, dkbl, isshop, type, shopid, adid, profitratio, appkey, appsecret,
                        linebalance, pid, dappkey, sqappkey, overdraft, appstore, appid, keypath, agentprefix, oneagent,
                        sxxh, isauth, isalipay, isprofit, isaliprofit, aliprofitratio, aliprofitmoney, profitmoney,
                        shopblanceAddPass, igtype, calltype, smstype, isgrounding, isupdateshop, ismember, isdialogbox,
                        browse, blanceAddPass);
                int code = usersService.updateByPrimaryKeySelective(record);
                int oemCode = usersService.updateByPrimaryKeySelective(userOem);
                String usernamesys = (String) request.getSession().getAttribute("useracc");

                if (blanceChoose == 1) {
                    if (code == 1 && oemCode == 1) {
                        logsService.insert(CommContent.TYPE_DELET_HUAZHANG,
                                "用户：" + usernamesys + "划账" + pass + ",到" + user.getUsername(), usernamesys, true,
                                IP.getIpAddr(request));
                        out.print(SUCCESS);
                    } else {
                        logsService.insert(CommContent.TYPE_DELET_HUAZHANG, "用户：" + usernamesys + "划账时失败了", usernamesys,
                                true, IP.getIpAddr(request));
                        out.print("更改失败，请您重试");
                    }
                }
                if (blanceChoose == 2) {
                    if (code == 1 && oemCode == 1) {
                        logsService.insert(CommContent.TYPE_DELET_HUAYUE,
                                "用户：" + usernamesys + "划购物余额" + pass + "到" + user.getUsername(), usernamesys, true,
                                IP.getIpAddr(request));
                        out.print(SUCCESS);
                    } else {
                        logsService.insert(CommContent.TYPE_DELET_HUAYUE, "用户：" + usernamesys + "划购物余额时失败了",
                                usernamesys, true, IP.getIpAddr(request));
                        out.print("更改失败，请您重试");
                    }
                }

            }
        }

    }

    public int updateAllShop(String parentId, Integer callType) {
        Connection conn = null;
        PreparedStatement pstm = null;

        int returnStatus = 0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(CommUrls.JDBC, CommUrls.JDBC_USER, CommUrls.JDBC_PASSWORD);

            String sql = "update users set CallType = ?  where ParentId = ? ";
            pstm = conn.prepareStatement(sql);

            pstm.setInt(1, callType);
            pstm.setString(2, parentId);
            pstm.executeUpdate();
            returnStatus = 1;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            if (pstm != null) {
                try {
                    pstm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
        }
        return returnStatus;
    }
}
